﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itegracion.Framework.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Itegracion.Framework.Model
{
    public class Constante
    {
        public static List<Element> TraerListado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  id, codigo, oid, nombre, valor
                FROM
                  Constante
                ORDER BY
                  id, codigo, oid");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> Traer(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  id, codigo, oid, nombre, valor
                FROM
                  Constante
                WHERE
                  id = @id");
            db.AddInParameter(command, "id", DbType.String, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.AddRange(dt.Rows.ToList());

            return rpta;
        }
        public static List<Element> GenerarOID(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT ISNULL(MAX(oid)+1, 0)
                FROM Constante 
                WHERE Codigo = @codigo");
            db.AddInParameter(command, "codigo", DbType.String, entrada[0].Value);
            object oid = db.ExecuteScalar(command);
            rpta.Add(new Element("rpta", oid));

            return rpta;
        }
        public static List<Element> Eliminar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                DELETE FROM Constante WHERE id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Insertar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                INSERT INTO Constante(codigo, oid, nombre, valor) VALUES(@codigo, @oid, @nombre, @valor)");
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "oid", DbType.Int32, entrada[2].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "valor", DbType.String, entrada[4].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Modificar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                UPDATE Constante SET codigo = @codigo, oid = @oid, nombre = @nombre, valor=@valor WHERE id = @id");
            db.AddInParameter(command, "id", DbType.String, entrada[0].Value);
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "oid", DbType.Int32, entrada[2].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "valor", DbType.String, entrada[4].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> TraerCombo(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  oid as id,
                  nombre
                FROM
                  constante
                WHERE
                  codigo = @codigo
                ORDER BY 1");
            db.AddInParameter(command, "codigo", DbType.String, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> TraerComboAbreviado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  oid as id,
                  valor as nombre
                FROM
                  constante
                WHERE
                  codigo = @codigo
                ORDER BY 1");
            db.AddInParameter(command, "codigo", DbType.String, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> TraerSubCombo(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            string txt = string.Format(@"
                SELECT
                  oid as id,
                  nombre
                FROM
                  constante
                WHERE
                  codigo = @codigo
                  AND oid IN ({0})
                ORDER BY 1", entrada[1].Value);
            DbCommand command = db.GetSqlStringCommand(txt);
            db.AddInParameter(command, "codigo", DbType.String, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> TraerGrupos(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT DISTINCT 
	                codigo as id,
	                codigo as nombre
                FROM 
	                Constante");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> TraerConfiguracion(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT 
	                oid as id, valor
                FROM 
	                Constante 
                WHERE 
	                codigo = 'Sistema'");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
    }
}
